﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VideoClub.Entities;

namespace VideoClub.DataAccess.Consultas
{
    class ConsultasDAO
    {
        public static List<PrimerConsultaView> ObtenerTodosLosDatosConsulta1(String letra1, String letra2, String genero)
        {
            List<PrimerConsultaView> list = new List<PrimerConsultaView>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @" select s.NOMBRE + ' ' + s.APELLIDO 'Nombre Y apellido', a.ID_ALQUILER 'Id Alquiler',
                                a.fecha_alquiler 'Fecha', count (pa.ID_PELICULA_X_ALQUILER) 'Peliculas Alquiladas',
                                sum (p.PRECIO_UNITARIO) 'Total', avg (p.PRECIO_UNITARIO) 'Promedio', g.DESCRIPCION 'Genero'
                                from socios s, alquiler a, PELICULAS_X_ALQUILER pa, PELICULAS p, GENERO g
                                where s.ID_SOCIO = a.ID_SOCIO
                                and a.ID_ALQUILER = pa.ID_ALQUILER
                                and p.ID_PELICULA = pa.ID_PELICULA
                                and p.ID_GENERO = g.ID_GENERO
                                and s.NOMBRE not like '[" + letra1 + "-" + letra2 + "]%' and g.DESCRIPCION like @genero GROUP BY A.FECHA_ALQUILER, g.DESCRIPCION, s.NOMBRE, s.APELLIDO, a.ID_ALQUILER HAVING count (pa.ID_PELICULA_X_ALQUILER) < (select avg (ap1.ID_PELICULA_X_ALQUILER) from PELICULAS_X_ALQUILER ap1)order by NOMBRE asc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("letra1", letra1);
                cmd.Parameters.AddWithValue("letra2", letra2);
                cmd.Parameters.AddWithValue("genero", genero);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadPrimerConsulta(reader));
                }
            }

            return list;
        }

        private static PrimerConsultaView LoadPrimerConsulta(SqlDataReader reader)
        {
            try
            {
                PrimerConsultaView primerConsulta = new PrimerConsultaView();

                primerConsulta.NombreCompleto = Convert.ToString(reader["Nombre Y apellido"]);
                primerConsulta.NumeroDeAlquiler = Convert.ToInt32(reader["Id Alquiler"]);
                primerConsulta.FechaDeAlquiler = Convert.ToDateTime(reader["Fecha"]);
                primerConsulta.CantPeliculasAlquiladas = Convert.ToInt32(reader["Peliculas Alquiladas"]);
                primerConsulta.Total = Convert.ToInt32(reader["Total"]);
                primerConsulta.Promedio = Convert.ToInt32(reader["Promedio"]);
                primerConsulta.Genero = Convert.ToString(reader["Genero"]); ;
                return primerConsulta;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }

        }

        public static List<SegundaConsultaView> ObtenerTodosLosDatosConsulta2(StringBuilder lista, String nombreSocio)
        {
            List<SegundaConsultaView> list = new List<SegundaConsultaView>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"select a.FECHA_ALQUILER 'Fecha', s.nombre + ' ' + s.apellido 'Nombre del CLiente', ps.ID_PELICULA 'Id Pelicula', m.DESCRIPCION 'Genero',
                                COUNT (ms.ID_SOCIO) 'Multas', SUM (m.IMPORTE) 'Importe', avg (m.IMPORTE) 'Promedio' 
                                from SOCIOS s, ALQUILER a, PELICULAS p, MULTAS m, MULTAS_X_SOCIOS ms, PELICULAS_X_ALQUILER ps
                                where s.ID_SOCIO = ms.ID_SOCIO
                                and m.ID_MULTAS = ms.ID_MULTA
                                and a.ID_SOCIO = s.ID_SOCIO
                                and p.ID_PELICULA = ps.ID_PELICULA
                                and a.ID_ALQUILER = ps.ID_ALQUILER
                                and p.ID_PELICULA = ms.ID_PELICULA
                                and m.DESCRIPCION not in (" + lista + @")
                                and s.NOMBRE not like '" + nombreSocio + @"'
                                and year (a.FECHA_ALQUILER) = year (getdate())
                                group by a.FECHA_ALQUILER, s.NOMBRE, s.APELLIDO, m.DESCRIPCION, ps.ID_PELICULA
                                having SUM (m.IMPORTE) < (select avg (m1.IMPORTE) from MULTAS m1)
                                order by s.APELLIDO DESC ";

                SqlCommand cmd = new SqlCommand(sql, conn);
                //cmd.Parameters.AddWithValue("letra1", letra1);
                //cmd.Parameters.AddWithValue("letra2", letra2);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadSegundaConsulta(reader));
                }
            }

            return list;
        }

        private static SegundaConsultaView LoadSegundaConsulta(SqlDataReader reader)
        {
            try
            {
                var segundaConsulta = new SegundaConsultaView();

                segundaConsulta.FechaDeAlquiler = Convert.ToDateTime(reader["Fecha"]);
                segundaConsulta.NombreCompleto = Convert.ToString(reader["Nombre del CLiente"]);
                segundaConsulta.IdPelicula = Convert.ToInt32(reader["Id Pelicula"]);
                segundaConsulta.Genero = Convert.ToString(reader["Genero"]);
                segundaConsulta.Multa = Convert.ToInt32(reader["Multas"]);
                segundaConsulta.Importe = Convert.ToInt32(reader["Importe"]);
                segundaConsulta.Promedio = Convert.ToDecimal(reader["Promedio"]); ;
                return segundaConsulta;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        public static List<TercerConsultaView> ObtenerTodosLosDatosConsulta3(String apellidoSocio, String genero)
        {
            List<TercerConsultaView> list = new List<TercerConsultaView>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"select a.ID_ALQUILER 'Id Alquiler',s.nombre + ' ' + s.apellido 'Nombre Y Apellido',A.FECHA_ALQUILER 'Fecha',
                                count (p.ID_GENERO) 'Total Por Genero',
                                g.DESCRIPCION 'Genero',sum (p.PRECIO_UNITARIO) 'Importe Total',
                                avg (p.PRECIO_UNITARIO) 'Promedio General'
                                from socios s, alquiler a, PELICULAS_X_ALQUILER pa, PELICULAS p, GENERO g
                                where s.ID_SOCIO = a.ID_SOCIO
                                and a.ID_ALQUILER = pa.ID_ALQUILER
                                and p.ID_PELICULA = pa.ID_PELICULA
                                and p.ID_GENERO = g.ID_GENERO
                                AND G.DESCRIPCION = '" + genero + @"'
                                and year (a.FECHA_ALQUILER) = year (getdate())
                                and s.APELLIDO not like '" + apellidoSocio + @"'
                                group by g.DESCRIPCION, s.NOMBRE, s.APELLIDO, a.ID_ALQUILER, A.FECHA_ALQUILER
                                having sum (p.PRECIO_UNITARIO) > (select avg (p1.precio_unitario) from peliculas p1)
                                ";

                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadTerceraConsulta(reader));
                }
            }

            return list;

        }

        private static TercerConsultaView LoadTerceraConsulta(SqlDataReader reader)
        {
            try
            {
                var tercerConsulta = new TercerConsultaView();

                tercerConsulta.IdAlquiler = Convert.ToInt32(reader["Id Alquiler"]);
                tercerConsulta.NombreCompleto = Convert.ToString(reader["Nombre Y Apellido"]);
                tercerConsulta.FechaDeAlquiler = Convert.ToDateTime(reader["Fecha"]);
                tercerConsulta.TotalPorGenero = Convert.ToInt32(reader["Total Por Genero"]);
                tercerConsulta.Genero = Convert.ToString(reader["Genero"]);
                tercerConsulta.ImporteTotal = Convert.ToInt32(reader["Importe Total"]);
                tercerConsulta.Promedio = Convert.ToDecimal(reader["Promedio General"]); ;
                return tercerConsulta;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        public static List<CuartoConsultaView> ObtenerTodosLosDatosConsulta4 (int primerA, int segundoA, String nombreDir)
        {
            List<CuartoConsultaView> list = new List<CuartoConsultaView>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT P.TITULO 'Titulo', P.ANIO 'AÑO de estreno', G.DESCRIPCION 'Genero', D.NOMBRE + ' ' + D.APELLIDO 'Nombre', 
                                A.FECHA_ALQUILER 'Fecha de Alquiler',COUNT (PA.ID_PELICULA_X_ALQUILER) 'Cantidad veces alquilada',SUM (P.PRECIO_UNITARIO) 'Importe Total',
                                AVG (P.PRECIO_UNITARIO) 'Promedio'
                                FROM ALQUILER A, PELICULAS P , PELICULAS_X_ALQUILER PA, DIRECTOR D, GENERO G
                                WHERE D.ID_DIRECTOR = P.ID_DIRECTOR
                                AND G.ID_GENERO = P.ID_GENERO
                                AND P.ID_PELICULA = PA.ID_PELICULA
                                AND A.ID_ALQUILER = PA.ID_ALQUILER
                                AND D.NOMBRE LIKE '" + nombreDir + @"'
                                AND YEAR(A.FECHA_ALQUILER) = YEAR (GETDATE())
                                AND P.ANIO BETWEEN " + primerA + " AND " + segundoA + @"
                                GROUP BY D.NOMBRE, D.APELLIDO, P.TITULO, G.DESCRIPCION, A.FECHA_ALQUILER, P.ANIO
                                HAVING SUM (P.PRECIO_UNITARIO) > (SELECT AVG (P1.PRECIO_UNITARIO) FROM PELICULAS P1) 
                                ORDER BY P.ANIO DESC ";

                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadCuartaConsulta(reader));
                }
            }

            return list;
        }

        private static CuartoConsultaView LoadCuartaConsulta(SqlDataReader reader)
        {
            try
            {
                var cuartaConsulta = new CuartoConsultaView();
                cuartaConsulta.TituloPelicula = Convert.ToString(reader["Titulo"]);
                cuartaConsulta.Anio = Convert.ToInt32(reader["AÑO de estreno"]);
                cuartaConsulta.Genero = Convert.ToString(reader["Genero"]);
                cuartaConsulta.NombreCompletoDirector = Convert.ToString(reader["Nombre"]);
                cuartaConsulta.FechaDeAlquiler = Convert.ToString(reader["Fecha de Alquiler"]);
                cuartaConsulta.CantVecesAlquilada = Convert.ToInt32(reader["Cantidad veces alquilada"]);
                cuartaConsulta.Importe = Convert.ToInt32(reader["Importe Total"]);
                cuartaConsulta.Promedio = Convert.ToDecimal(reader["Promedio"]); ;
                return cuartaConsulta;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        public static List<QuintaConsultaView> ObtenerTodosLosDatosConsulta5(String nombreDir, String formato)
        {
            List<QuintaConsultaView> list = new List<QuintaConsultaView>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"select s.nombre + ' ' + s.APELLIDO 'Nombre Socio', A.FECHA_ALQUILER 'Fecha', D.NOMBRE + ' ' + D.APELLIDO 'Nombre Director',
                                P.TITULO 'Titulo', f.DESCRIPCION 'Formato'
                                from socios s, director d, alquiler a, PELICULAS_X_ALQUILER pa, FORMATO f, PELICULAS P
                                WHERE S.ID_SOCIO = A.ID_SOCIO
                                AND F.ID_FORMATO= P.ID_FORMATO
                                AND D.ID_DIRECTOR = P.ID_DIRECTOR
                                AND A.ID_ALQUILER = PA.ID_ALQUILER
                                AND P.ID_PELICULA = PA.ID_PELICULA
                                and f.DESCRIPCION = '" + formato + @"'
                                and d.NOMBRE like '"+nombreDir+@"'
                                AND 10 > (SELECT COUNT (A1.ID_SOCIO) FROM ALQUILER A1 WHERE YEAR (A1.FECHA_ALQUILER) = YEAR (GETDATE()))
                                ";

                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadQuintaConsulta(reader));
                }
            }

            return list;
        }

        private static QuintaConsultaView LoadQuintaConsulta(SqlDataReader reader)
        {
            try
            {
                var quintaConsulta = new QuintaConsultaView();
                quintaConsulta.NombreSocio = Convert.ToString(reader["Nombre Socio"]);
                quintaConsulta.Fecha = Convert.ToDateTime(reader["Fecha"]);
                quintaConsulta.NombreDirector = Convert.ToString(reader["Nombre Director"]);
                quintaConsulta.Titulo = Convert.ToString(reader["Titulo"]);
                quintaConsulta.Formato = Convert.ToString(reader["Formato"]);

                return quintaConsulta;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }
    }

}
